<!DOCTYPE html>
<html lang="zh-cn">
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <title>MySQL主从复制 - 古一 - 个人博客</title>
  <meta name="renderer" content="webkit" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>

<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />

<meta name="theme-color" content="#f8f5ec" />
<meta name="msapplication-navbutton-color" content="#f8f5ec">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="#f8f5ec">


<meta name="author" content="古一" /><meta name="description" content="1. 概念 MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实" /><meta name="keywords" content="古一, 博客, 网站" />






<meta name="generator" content="Hugo 0.81.0 with theme even" />


<link rel="canonical" href="/post/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/" />
<link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png">
<link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
<link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png">
<link rel="manifest" href="/manifest.json">
<link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5">



<link href="/sass/main.min.af7fd1da18d66c2b017df5b4cae508ef44cfcac3fb4c7c7a327fe4f4f9e28b08.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.1.20/dist/jquery.fancybox.min.css" integrity="sha256-7TyXnr2YU040zfSP+rEcz29ggW4j56/ujTPwjMzyqFY=" crossorigin="anonymous">


<meta property="og:title" content="MySQL主从复制" />
<meta property="og:description" content="1. 概念 MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实" />
<meta property="og:type" content="article" />
<meta property="og:url" content="/post/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/" /><meta property="article:section" content="post" />
<meta property="article:published_time" content="2021-05-23T13:08:00&#43;08:00" />
<meta property="article:modified_time" content="2021-05-23T13:08:00&#43;08:00" />

<meta itemprop="name" content="MySQL主从复制">
<meta itemprop="description" content="1. 概念 MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实"><meta itemprop="datePublished" content="2021-05-23T13:08:00&#43;08:00" />
<meta itemprop="dateModified" content="2021-05-23T13:08:00&#43;08:00" />
<meta itemprop="wordCount" content="672">
<meta itemprop="keywords" content="MySQL," /><meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="MySQL主从复制"/>
<meta name="twitter:description" content="1. 概念 MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实"/>

<!--[if lte IE 9]>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
<![endif]-->

<!--[if lt IE 9]>
  <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
<![endif]-->

</head>
<body>
  <div id="mobile-navbar" class="mobile-navbar">
  <div class="mobile-header-logo">
    <a href="/" class="logo">古一</a>
  </div>
  <div class="mobile-navbar-icon">
    <span></span>
    <span></span>
    <span></span>
  </div>
</div>
<nav id="mobile-menu" class="mobile-menu slideout-menu">
  <ul class="mobile-menu-list">
    <a href="/">
        <li class="mobile-menu-item">首页</li>
      </a><a href="/post/">
        <li class="mobile-menu-item">文章</li>
      </a><a href="/tags/">
        <li class="mobile-menu-item">标签</li>
      </a><a href="/categories/">
        <li class="mobile-menu-item">分类</li>
      </a><a href="/about/">
        <li class="mobile-menu-item">关于</li>
      </a>
  </ul>

  


</nav>

  <div class="container" id="mobile-panel">
    <header id="header" class="header">
        <div class="logo-wrapper">
  <a href="/" class="logo">古一</a>
</div>





<nav class="site-navbar">
  <ul id="menu" class="menu">
    <li class="menu-item">
        <a class="menu-item-link" href="/">首页</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/post/">文章</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/tags/">标签</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/categories/">分类</a>
      </li><li class="menu-item">
        <a class="menu-item-link" href="/about/">关于</a>
      </li>
  </ul>
</nav>

    </header>

    <main id="main" class="main">
      <div class="content-wrapper">
        <div id="content" class="content">
          <article class="post">
    
    <header class="post-header">
      <h1 class="post-title">MySQL主从复制</h1>

      <div class="post-meta">
        <span class="post-time"> 2021-05-23 </span>
        <div class="post-category">
            <a href="/categories/%E6%8A%80%E6%9C%AF/"> 技术 </a>
            </div>
          <span class="more-meta"> 约 672 字 </span>
          <span class="more-meta"> 预计阅读 2 分钟 </span>
        
      </div>
    </header>

    <div class="post-toc" id="post-toc">
  <h2 class="post-toc-title">文章目录</h2>
  <div class="post-toc-content">
    <nav id="TableOfContents">
  <ul>
    <li>
      <ul>
        <li><a href="#1-概念">1. 概念</a></li>
        <li><a href="#2-实现mysql主从复制需要进行的配置">2. 实现MySQL主从复制需要进行的配置</a></li>
      </ul>
    </li>
  </ul>
</nav>
  </div>
</div>
    <div class="post-content">
      <h2 id="1-概念">1. 概念</h2>
<blockquote>
<p>MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。</p>
</blockquote>
<p>​	一个多个数据库间主从复制与读写分离的模型(来源网络)：</p>
<p><img src="/blog/uploads/20210120/mysql1.jpg" alt="Local Picture" title="MySQL主从复制与读写分离模型图"></p>
<p>在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。</p>
<p><img src="/blog/uploads/20210120/mysql2.jpg" alt="Local Picture" title="MySQL主从复制与读写分离模型图"></p>
<p>MySQL之间数据复制的基础是二进制日志文件（binary log  file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。</p>
<h2 id="2-实现mysql主从复制需要进行的配置">2. 实现MySQL主从复制需要进行的配置</h2>
<ul>
<li>
<p>主服务器：</p>
<ul>
<li>开启二进制日志</li>
<li>配置唯一的server-id</li>
<li>获得master二进制日志文件名及位置</li>
<li>创建一个用于slave和master通信的用户账号</li>
</ul>
</li>
<li>
<p>从服务器：</p>
<ul>
<li>配置唯一的server-id</li>
</ul>
</li>
<li>
<ul>
<li>使用master分配的用户账号读取master二进制日志</li>
<li>启用slave服务</li>
</ul>
</li>
</ul>

    </div>

    <div class="post-copyright">
  <p class="copyright-item">
    <span class="item-title">文章作者</span>
    <span class="item-content">古一</span>
  </p>
  <p class="copyright-item">
    <span class="item-title">上次更新</span>
    <span class="item-content">
        2021-05-23
        
    </span>
  </p>
  
  <p class="copyright-item">
    <span class="item-title">许可协议</span>
    <span class="item-content">MIT</span>
  </p>
</div>
<footer class="post-footer">
      <div class="post-tags">
          <a href="/tags/mysql/">MySQL</a>
          </div>
      <nav class="post-nav">
        <a class="prev" href="/post/rabbitmq/">
            <i class="iconfont icon-left"></i>
            <span class="prev-text nav-default">RabbitMQ消息队列</span>
            <span class="prev-text nav-mobile">上一篇</span>
          </a>
        <a class="next" href="/post/mysql/mysql%E5%85%B1%E4%BA%AB%E9%94%81%E4%B8%8E%E6%8E%92%E4%BB%96%E9%94%81/">
            <span class="next-text nav-default">MySQL中的共享锁与排他锁</span>
            <span class="next-text nav-mobile">下一篇</span>
            <i class="iconfont icon-right"></i>
          </a>
      </nav>
    </footer>
  </article>
        </div>
        

  

  

      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="social-links">
  <a href="/index.xml" type="application/rss+xml" class="iconfont icon-rss" title="rss"></a>
</div>

<div class="copyright">
  <span class="power-by">
    由 <a class="hexo-link" href="https://gohugo.io">Hugo</a> 强力驱动
  </span>
  <span class="division">|</span>
  <span class="theme-info">
    主题 - 
    <a class="theme-link" href="https://github.com/olOwOlo/hugo-theme-even">Even</a>
  </span>

  

  <span class="copyright-year">
    <span><a href="https://beian.miit.gov.cn/">粤ICP备20006718号-1</a></span>
    &copy; 
    2018 - 
    2022<span class="heart"><i class="iconfont icon-heart"></i></span><span>古一</span>
  </span>
</div>

    </footer>

    <div class="back-to-top" id="back-to-top">
      <i class="iconfont icon-up"></i>
    </div>
  </div>
  
  <script src="https://cdn.jsdelivr.net/npm/jquery@3.2.1/dist/jquery.min.js" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/slideout@1.0.1/dist/slideout.min.js" integrity="sha256-t+zJ/g8/KXIJMjSVQdnibt4dlaDxc9zXr/9oNPeWqdg=" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.1.20/dist/jquery.fancybox.min.js" integrity="sha256-XVLffZaxoWfGUEbdzuLi7pwaUJv1cecsQJQqGLe7axY=" crossorigin="anonymous"></script>



<script type="text/javascript" src="/js/main.min.c99b103c33d1539acf3025e1913697534542c4a5aa5af0ccc20475ed2863603b.js"></script>
  <script type="text/javascript">
    window.MathJax = {
      tex: {
        }
    };
  </script>
  <script async src="https://cdn.jsdelivr.net/npm/mathjax@3.0.5/es5/tex-mml-chtml.js" integrity="sha256-HGLuEfFcsUJGhvB8cQ8nr0gai9EucOOaIxFw7qxmd+w=" crossorigin="anonymous"></script>








</body>
</html>
